import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.CountDownLatch;

import org.h2.jdbc.JdbcSQLException;
import org.junit.Test;

public class DbTest {
    private volatile Exception ex;

    @Test
    public void testJoin() throws Exception {
        try (Connection con = DriverManager.getConnection("jdbc:h2:mem:testdb")) {
            try(PreparedStatement ps = con.prepareStatement("create table users(id bigint identity, name varchar(255))")){ps.execute();};

            try(PreparedStatement ps = con.prepareStatement("insert into users(name) values('Вася')")) {ps.execute();}
            try(PreparedStatement ps = con.prepareStatement("insert into users(name) values('Петя')")) {ps.execute();}
            try(PreparedStatement ps = con.prepareStatement("insert into users(name) values('Маша')")) {ps.execute();}

            try(PreparedStatement ps = con.prepareStatement("create table cars(id bigint identity, model varchar(255), user_id bigint not null," +
                    "CONSTRAINT CARS_FK1 FOREIGN KEY (USER_ID) REFERENCES USERS (ID) ON DELETE CASCADE ON UPDATE CASCADE)")){ps.execute();};

    		try(PreparedStatement ps = con.prepareStatement("insert into cars(model, user_id) values('BMW', 1)")) {ps.execute();}
    		try(PreparedStatement ps = con.prepareStatement("insert into cars(model, user_id) values('VAZ', 1)")) {ps.execute();}
    		try(PreparedStatement ps = con.prepareStatement("insert into cars(model, user_id) values('BMW', 2)")) {ps.execute();}


    		try(PreparedStatement ps = con.prepareStatement("SELECT u.NAME, c.MODEL from USERS u LEFT JOIN CARS c on u.ID = c.USER_ID")) {
    		    try(ResultSet rs = ps.executeQuery()) {
    		        System.out.println(rs);
    		    }
		    }

        }
    }

    @Test(expected = JdbcSQLException.class)
    public void testMvcc() throws Exception {
        final CountDownLatch latch = new CountDownLatch(1);

        try (Connection sentinelCon = DriverManager.getConnection("jdbc:h2:mem:testdb")) {
            try(PreparedStatement ps = sentinelCon.prepareStatement("create table test(id bigint identity, name varchar(255))")){ps.execute();};
            try(PreparedStatement ps = sentinelCon.prepareStatement("INSERT INTO test(name) values('Вася')")) {ps.execute();}
            try(PreparedStatement ps = sentinelCon.prepareStatement("INSERT INTO test(name) values('Маша')")) {ps.execute();}

            Thread t1 = new Thread() {
                @Override
                public void run() {
                    try {
                        try(Connection con = DriverManager.getConnection("jdbc:h2:mem:testdb")) {
                            try(PreparedStatement ps = con.prepareStatement("select * from test where id = ?")) {
                                ps.setInt(1, 2);

                                latch.await();

                                ps.executeQuery();
                            }
                        }
                    } catch (Exception e) {
                        DbTest.this.ex = e;
                    }
                };
            };

            Thread t2 = new Thread() {
                @Override
                public void run() {
                    try {
                        try(Connection con = DriverManager.getConnection("jdbc:h2:mem:testdb")) {
                            con.setAutoCommit(false);

                            try(PreparedStatement ps = con.prepareStatement("update test set name='Петя' where id = ?")) {
                                ps.setInt(1, 1);

                                ps.executeUpdate();

                                latch.countDown();

                                Thread.sleep(10000);

                                con.commit();
                            } catch (Exception e) {
                                con.rollback();
                            }
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                };
            };

            t1.start();
            t2.start();
            t1.join();
            t2.join();

            System.out.println(ex.getMessage());

            throw ex;
        }
    }

    @Test
    public void testMetadata() throws Exception {
        try (Connection con = DriverManager.getConnection("jdbc:h2:mem:testdb")) {
            try(PreparedStatement ps = con.prepareStatement("create table test(id bigint identity, name varchar(255))")){ps.execute();};
            try(PreparedStatement ps = con.prepareStatement("INSERT INTO test(name) values('Вася')")) {ps.execute();}
            try(PreparedStatement ps = con.prepareStatement("INSERT INTO test(name) values('Маша')")) {ps.execute();}

            DatabaseMetaData meta = con.getMetaData();

            ResultSet rs1 = meta.getPrimaryKeys("TESTDB", "PUBLIC", "TEST");

            rs1.next();

            System.out.println(rs1.getString(6));
        }
    }
}